Math Functions

The math functions (min, max, avg) allow you to perform statistical calculations on numeric fields and compare field values against these calculated statistics. These functions are particularly useful for identifying outliers, finding issues above or below certain thresholds, and performing statistical analysis on your issue data.

Available Functions

min(jql)

Returns the minimum value from the specified JQL subquery.

Syntax:

"field_name" operator min("jql_subquery")

Example:

"Story Points" > min("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is greater than the minimum "Story Points" value in the DEVELOPMENT project.

max(jql)

Returns the maximum value from the specified JQL subquery.

Syntax:

"field_name" operator max("jql_subquery")

Example:

"Story Points" < max("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is less than the maximum "Story Points" value in the DEVELOPMENT project.

avg(jql)

Returns the average (mean) value from the specified JQL subquery.

Syntax:

"field_name" operator avg("jql_subquery")

Example:

"Story Points" > avg("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is greater than the average "Story Points" value in the DEVELOPMENT project.

Available Functions

min(jql)

Returns the minimum value from the specified JQL subquery.

Example:

"Story Points" > min("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is greater than the minimum "Story Points" value in the DEVELOPMENT project.

max(jql)

Returns the maximum value from the specified JQL subquery.

Example:

"Story Points" < max("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is less than the maximum "Story Points" value in the DEVELOPMENT project.

avg(jql)

Returns the average (mean) value from the specified JQL subquery.

Example:

"Story Points" > avg("project = DEVELOPMENT")

This query finds all issues where the "Story Points" value is greater than the average "Story Points" value in the DEVELOPMENT project.

Parameters

• jql_subquery (required): A valid JQL query that defines the set of issues to evaluate for the statistical calculation.
• field_name (required): The name of the numeric field to compare against the calculated statistic.
• operator (required): One of the supported comparison operators: =, !=, >, >=, <, <=

Usage Notes

1. Function Names Should Not Be Quoted: Use min, max, and avg as functions without quotes.
2. Numeric Fields Only: These functions work exclusively with numeric fields (number fields, story points, etc.).
3. Subquery Requirements: The subquery must return issues that have values in the field being compared.
4. Real-time Calculations: Results are calculated based on the current values of the fields at the time of the query.
5. Empty Field Handling: If the subquery returns issues with empty numeric fields, those values are excluded from the calculation.

Syntax

The general syntax for using math functions in JQL is:

"field_name" operator function("jql_subquery")

Where:

• field_name is the name of a numeric field
• operator is one of: =, !=, >, >=, <, <=
• function is one of: min, max, avg
• jql_subquery is a valid JQL query that returns a set of issues

Examples

Basic Statistical Comparisons

Finding issues with story points above project average:

"Story Points" > avg("project = DEVELOPMENT")

Finding issues with story points below project maximum:

"Story Points" < max("project = DEVELOPMENT")

Finding issues with story points equal to project minimum:

"Story Points" = min("project = DEVELOPMENT")

Finding issues with story points not equal to project average:

"Story Points" != avg("project = DEVELOPMENT")

Filtered Statistical Comparisons

Finding issues with story points above average in a specific component:

"Story Points" > avg("component = 'Frontend'")

Finding issues with story points below maximum in a specific sprint:

"Story Points" < max("sprint = 'Sprint 42'")

Finding issues with effort above average for high-priority issues:

"Effort" > avg("priority = High")

Complex Statistical Queries

Finding issues where story points are between min and max of effort field:

"Story Points" >= min("project = DEVELOPMENT AND \"Effort\" IS NOT EMPTY") 
AND "Story Points" <= max("project = DEVELOPMENT AND \"Effort\" IS NOT EMPTY")

Finding issues with story points above average of completed issues:

"Story Points" > avg("status = Done")

Finding issues with story points below maximum of in-progress issues:

"Story Points" < max("status = 'In Progress'")

Best Practices

1. Always provide a valid JQL subquery:

   • The subquery must be a valid JQL expression
   • The subquery should return a set of issues that have values in the field you're comparing against
   • Use specific filters to limit the result set for better performance

2. Consider performance implications:

   • Complex subqueries may impact query performance
   • Avoid using very broad subqueries that return many issues
   • Use more specific filters in your subqueries when possible

3. Use appropriate comparison operators:

   • Use = and != for exact matches
   • Use >, >=, <, <= for range comparisons
   • Consider the statistical meaning of your comparisons

4. Combine with other JQL functions:

   • Math functions can be combined with other JQL functions for more complex queries
   • Use parentheses to ensure proper operator precedence
   • Consider using fieldValue function for more complex field comparisons

5. Handle empty fields appropriately:

   • Use IS NOT EMPTY filters in subqueries when you want to exclude empty values
   • Be aware that empty fields are excluded from statistical calculations

Error Handling

The functions will return an error if:

1. The specified field name is not valid or not a numeric field
2. The JQL subquery is invalid or returns no results
3. The function name is not properly quoted (min, max, avg are reserved words)
4. The comparison operator is not supported
5. All issues in the subquery have empty values for the field being calculated

Performance Considerations

1. Subquery Size:

   • Large subqueries may impact performance
   • Consider using more specific subqueries to limit the result set
   • Use filters like IS NOT EMPTY to reduce the number of issues processed

2. Field Type Impact:

   • Numeric field comparisons are generally fast
   • Complex calculations on large datasets may require additional processing

3. Result Set Size:

   • Large result sets may impact performance
   • Use appropriate subqueries to limit the number of issues to search through

4. Real-time Calculations:

   • Statistics are calculated in real-time based on current field values
   • Changes to field values will immediately affect query results